using System.Data;
using System.Data.Common;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace ASPNETVN.PORTAL.Components.Helpers
{
    public class SearchHelper
    {
        public static DataSet Search(string searchText)
        {
            StringBuilder query = new StringBuilder();
            string[] words = searchText.Split(' ');

            query.Append("SELECT TOP 50 I.[ID], I.[Title], I.[SubTitle], C.[ModuleID] ");
            query.Append("FROM t_SHARE_Categories C INNER JOIN t_DOWNLOAD_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE ");

            foreach (string w in words)
            {
                query.AppendFormat("(I.Title LIKE N'%{0}%') AND ", w);
            }
            query = query.Remove(query.Length - 5, 5);


            query.Append("UNION SELECT TOP 50 I.[ID], I.[Title], I.[SubTitle], C.[ModuleID] ");
            query.Append("FROM t_SHARE_Categories C INNER JOIN t_CMS_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE ");

            foreach (string w in words)
            {
                query.AppendFormat("(I.Title LIKE N'%{0}%') AND ", w);
            }
            query = query.Remove(query.Length - 5, 5);

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(query.ToString());

            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetRecentItems()
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder query = new StringBuilder();

            // DOWNLOADS.
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_DOWNLOAD_Categories C INNER JOIN t_DOWNLOAD_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'DOWNLOADS' ");
            query.Append("ORDER BY I.[ID] DESC ");

            DbCommand dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsDownloads = db.ExecuteDataSet(dbCommand);

            // EBOOKS.
            query = new StringBuilder();
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_DOWNLOAD_Categories C INNER JOIN t_DOWNLOAD_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'EBOOKS' ");
            query.Append("ORDER BY I.[ID] DESC ");

            dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsEbooks = db.ExecuteDataSet(dbCommand);

            // NEWS.
            query = new StringBuilder();
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_CMS_Categories C INNER JOIN t_CMS_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'NEWS' ");
            query.Append("ORDER BY I.[ID] DESC ");

            dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsNews = db.ExecuteDataSet(dbCommand);

            // ARTICLES.
            query = new StringBuilder();
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_CMS_Categories C INNER JOIN t_CMS_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'ARTICLES' ");
            query.Append("ORDER BY I.[ID] DESC ");

            dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsArticles = db.ExecuteDataSet(dbCommand);

            // LEARNING.
            query = new StringBuilder();
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_CMS_Categories C INNER JOIN t_CMS_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'LEARNING' ");
            query.Append("ORDER BY I.[ID] DESC ");

            dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsLearning = db.ExecuteDataSet(dbCommand);

            // VIDEOS.
            query = new StringBuilder();
            query.Append("SELECT TOP 5 I.[ID], I.[Title], I.[SubTitle], C.[Module] ");
            query.Append("FROM t_CMS_Categories C INNER JOIN t_CMS_Items I ON C.ID = I.CategoryID ");
            query.Append("WHERE C.[Module] = 'VIDEOS' ");
            query.Append("ORDER BY I.[ID] DESC ");

            dbCommand = db.GetSqlStringCommand(query.ToString());
            DataSet dsVideos = db.ExecuteDataSet(dbCommand);


            // Merge data.
            foreach (DataRow row in dsArticles.Tables[0].Rows)
            {
                dsNews.Tables[0].ImportRow(row);
            }

            foreach (DataRow row in dsLearning.Tables[0].Rows)
            {
                dsNews.Tables[0].ImportRow(row);
            }

            foreach (DataRow row in dsDownloads.Tables[0].Rows)
            {
                dsNews.Tables[0].ImportRow(row);
            }

            foreach (DataRow row in dsEbooks.Tables[0].Rows)
            {
                dsNews.Tables[0].ImportRow(row);
            }

            foreach (DataRow row in dsVideos.Tables[0].Rows)
            {
                dsNews.Tables[0].ImportRow(row);
            }

            return dsNews;
        }
    }
}